﻿using ExcelSystem.Model;
using ExcelSystem.ToolClass;
using ExcelSystem.ViewModelsClass;
using ExcelSystem.Views;
using ImTools;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using Newtonsoft.Json;
using NPOI.HPSF;
using NPOI.SS.Formula;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Org.BouncyCastle.Asn1.X9;
using Prism.Services.Dialogs;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Documents;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.Tab;

namespace ExcelSystem.ToolHelper
{
    public static class ExcelUserHelperYM
    {
        static ExcelContext db = ExcelContentFactory.GetInstance();


        public static string ReadExcelYM()
        {
            string result = "";
            var fileNames = ExcelUserHelper.OpenExcelSystemDialogs();
            List<Order> OrderList = new List<Order>();
            foreach (var file in fileNames)
            {
                try
                {
                    using (FileStream fs = File.OpenRead(file))
                    {
                        //【1】.创建工作簿 2007之前用HSSFWorkbook 2007之后用6XSSFWorkbook  
                        IWorkbook workBook = null;
                        // 根据版本号创建不同版本的excel
                        workBook = WorkbookFactory.Create(fs);
                        // 获取工作表
                     
                        ISheet sheet = workBook.GetSheet("采购订单");
                        for (int i = 1; i <= sheet.LastRowNum; i++)
                        {
                            var row = sheet.GetRow(i);
                           
                            Order order = new Order();
                            Order orderbase = db.Orders.FirstOrDefault(t => t.OrderNo == sheet.GetRow(i).GetCell(0).ToString());
                            if (orderbase != null)
                                order = orderbase;
                            //订单号
                            order.OrderNo = sheet.GetRow(i).GetCell(0).ToString();
                            //物料描述
                            order.MaterialDescribe = sheet.GetRow(i).GetCell(12).ToString();
                            //合同号
                            order.ContractNo = sheet.GetRow(i).GetCell(22).ToString();
                            //交货日期
                            order.DeliveryDate = Convert.ToDateTime(sheet.GetRow(i).GetCell(17).ToString());
                            //下单日期
                            order.SchedulingDate = Convert.ToDateTime(sheet.GetRow(i).GetCell(3).ToString());
                            order.Company = 2;
                            order.CreateTime=DateTime.Now;
                            order.UpdateTime=DateTime.Now;
                            if (orderbase == null)
                                OrderList.Add(order);
                        }
                      



                    }
                }
                catch (Exception e)
                {

                    result = $" 导出失败，错误:{e.Message}";
                }
            }
            db.Orders.AddRange(OrderList);
            db.SaveChanges();
            return $"成功导入{OrderList.Count.ToString()}条";
        }

        public static string ReadExcelYM_MaterialNo()
        {
            string result = "";
            var fileNames = ExcelUserHelper.OpenExcelSystemDialogs();
            var date = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
            //获取今天添加的订单
           var db_order=  db.Orders.Where(t => t.CreateTime >= date && t.Company==2).ToList();
          
            List<Material> materialList = new List<Material>();
            foreach (var file in fileNames)
            {
                try
                {
                    using (FileStream fs = File.OpenRead(file))
                    {
                        //【1】.创建工作簿 2007之前用HSSFWorkbook 2007之后用6XSSFWorkbook  
                        IWorkbook workBook = null;
                        // 根据版本号创建不同版本的excel
                        workBook = WorkbookFactory.Create(fs);
                        // 获取工作表

                        ISheet sheet = workBook.GetSheet("外购整箱清单");
                        for (int i = 1; i <= sheet.LastRowNum; i++)
                        {
                            var row = sheet.GetRow(i);
                            var order = db_order.FirstOrDefault(t => t.ContractNo == row.GetCell(0).CellToString());
                            if (order==null)
                                continue;
                            //下层物料信息
                            Material material = new Material();
                            Material materialbase = db.Materials.FirstOrDefault(t => t.ContractNo == order.ContractNo && t.MaterialNo == row.GetCell(3).CellToString());
                            if (materialbase != null)
                                material = materialbase;
                            material.OrderNo = order.OrderNo;
                            material.ContractNo = order.ContractNo;
                            material.BoxNumner = row.GetCell(1).CellToString();
                  
                            if ((material.BoxNumner.Contains("18#") && order.MaterialDescribe == "承重钢梁") ||
                               (material.BoxNumner.Contains("35#") && order.MaterialDescribe == "底座箱"))
                            {
                                material.BoxName = row.GetCell(2).CellToString();
                                material.MaterialNo = row.GetCell(3).CellToString();
                                material.MaterialDescribe = row.GetCell(4).CellToString();
                                if (material.MaterialDescribe.Contains("包装"))
                                    continue;
                                material.Number = ConvetHelper.ToDecimal(row.GetCell(5).CellToString());
                               
                                material.Unit = row.GetCell(6).CellToString();
                          
                                material.BOMSpecifications = row.GetCell(7).CellToString();
                                material.MaterialSpecifications = row.GetCell(8).CellToString();
                              
                                material.Remarks = row.GetCell(9).CellToString();
                                if (material.MaterialNo == "XTA3243APB999")
                                {

                                }
                                //获取L
                                List<string> strings = new List<string>() { material.Remarks, material.MaterialSpecifications };
                                string l_str= strings.FirstOrDefault(t => t.Contains("L") || t.Contains("HD+K") || t.Contains("HW+K") );
                                if(l_str!=null)
                                {
                                    var L = l_str.Split(",").FirstOrDefault(t => t.Contains("L") || t.Contains("HD+K") || t.Contains("HW+K"));
                                    if (!string.IsNullOrEmpty(L))
                                    {


                                        material.L = L.Replace("=", "").Replace("L", "").Replace("HD+K", "").Replace("HW+K", "");
                                        material.Number = material.Number * 1000 / Convert.ToInt32(material.L);
                                    }
                                }
                                
                                if (materialbase == null)
                                    materialList.Add(material);
                                else
                                    db.Materials.Update(material);

                            }
                              
                         
                          



                        }




                    }
                }
                catch (Exception e)
                {

                    result = $" 导出失败，错误:{e.Message}";
                }
            }
            db.Materials.AddRange(materialList);
            db.SaveChanges();
            return $" 添加了{materialList.Count}条数据";
        }

        public static void PrintingYM(List<MaterialViewModel> material, IDialogService dialogService, BOM bom)
        {
            ExcelInfo excelInfo = new ExcelInfo();
            try
            {

                //获取主梁顶置
                var filters = db.Filters.FirstOrDefault(t => (t.Company == "优迈" || t.Company == "2") && t.Name.Contains("MaterialNo"));
                if (filters != null)
                {
                    var f_v = JsonConvert.DeserializeObject<FilterValue>(filters.Value);
                    if (f_v.OverHead != null)
                    {
                        List<MaterialViewModel> a = new List<MaterialViewModel>();
                        List<MaterialViewModel> b = new List<MaterialViewModel>();
                        material.ForEach(t =>
                        {
                            var head = f_v.OverHead.FindFirst(j => t.Material.MaterialNo.Contains(j));
                            if (head != null)
                            {
                                a.Add(t);
                            }
                            else
                            {
                                b.Add(t);
                            }


                        }

                        );
                        a.AddRange(b);
                        material = a;
                    }



                }
                else
                {
                    material = material.OrderByDescending(t => t.Material.Number).ToList();
                }



                bool check = true;
                //检测数据来源
                for (int i = 0; i < material.Count; i++)
                {
                    if ((material[0].Material.OrderNo != material[i].Material.OrderNo) && (material[0].Material.ContractNo != material[i].Material.ContractNo))
                    {
                        MessageBox.Show("所选项来源于同个订单号、合同号");
                        check = false;
                    }
                }
                if (!check)
                {
                    return;
                }

                string time = $"{DateTime.Now.Month}.{DateTime.Now.Day}";
                var path = System.IO.Directory.GetCurrentDirectory();
                var allPath = path + "/Excel/YM/" + time + "/";
                if (!Directory.Exists(allPath))
                {
                    Directory.CreateDirectory(allPath);
                }
                string readPath = "";
                //1 2 3 公用一个模板
                if (material.Count == 1 || material.Count == 2 || material.Count == 3 || material.Count == 4)
                {
                    readPath = path + "/Excel/" + "ym.xlsx";
                }

                //读取模板文件
                using (FileStream fs = System.IO.File.OpenRead(readPath))
                {
                    //获取订单信息
                    Order order = db.Orders.FirstOrDefault(t => t.OrderNo == material[0].Material.OrderNo && t.ContractNo == material[0].Material.ContractNo);
                    if (order == null)
                        return;

                    //【1】.创建工作簿 2007之前用HSSFWorkbook 2007之后用6XSSFWorkbook  
                    IWorkbook? workBook = null;
                    // 根据版本号创建不同版本的excel
                    workBook = new XSSFWorkbook(fs);
                    // 获取工作表
                    ISheet sheet = workBook.GetSheetAt(0);
                    //1
                    IRow row = sheet.GetRow(0);
                    row.GetCell(2).SetCellValue(DateTime.Now);//日期
                                                              //5                                       
                    IRow row1 = sheet.GetRow(1);
                    row1.GetCell(1).SetCellValue("11");//总图号前
                    row1.GetCell(3).SetCellValue("22");//总图号后
                    row1.GetCell(5).SetCellValue(order.OrderNo);//订单
                    row1.GetCell(8).SetCellValue(order.ContractNo);//合同号
                    row1.GetCell(13).SetCellValue(order.Number.ToString());//数量
                    row1.GetCell(14).SetCellValue(order.DeliveryDate);//交期

                    IRow row2 = sheet.GetRow(2);
                    string info_origin = string.IsNullOrEmpty(material[0].Material.Remarks) ? material[0].Material.MaterialSpecifications : material[0].Material.Remarks;
                    var remark_info = info_origin.Split(",").ToList();
                    var L = remark_info.FirstOrDefault(t => t.Contains("L"));
                    if (L != null)
                        L = L.Replace("L", "").Replace("=", "");
                    row2.GetCell(11).SetCellValue(L);//L
                    var AA = remark_info.FirstOrDefault(t => t.Contains("AA"));
                    if (AA != null)
                        AA = AA.Replace("AA", "").Replace("=", "");
                    row2.GetCell(13).SetCellValue(AA);//AA

                    IRow row3 = sheet.GetRow(3);
                    IRow row4 = sheet.GetRow(4);
                    var BOMChildInfos = JsonConvert.DeserializeObject<ObservableCollection<BOMChildInfo>>(bom.ChildJson);

                    var pars = JsonConvert.DeserializeObject<List<BOM_Info>>(bom.ChildPar);
                    if (BOMChildInfos.Count >= 1)
                    {
                        row3.GetCell(0).SetCellValue("1");
                        row3.GetCell(1).SetCellValue(BOMChildInfos[0].ChildNo);
                        row3.GetCell(2).SetCellValue(BOMChildInfos[0].ChildNumber);
                        row3.GetCell(4).SetCellValue(BOMChildInfos[0].M_Type);
                        row3.GetCell(5).SetCellValue(BOMChildInfos[0].Size);
                        row3.GetCell(6).SetCellValue(BOMChildInfos[0].Weight);
                        row3.GetCell(7).SetCellValue(BOMChildInfos[0].Number);
                        row3.GetCell(8).SetCellValue(BOMChildInfos[0].Remaker);
                        //运算公式
                        if (pars.Count >= 2)
                        {
                            row3.GetCell(10).SetCellValue(pars[0].Key);
                            row3.GetCell(11).SetCellValue(GetYMResult(pars[0].Cal.Replace("L", L).Replace("AA", AA)));
                            row3.GetCell(12).SetCellValue(pars[1].Key);
                            row3.GetCell(13).SetCellValue(GetYMResult(pars[1].Cal.Replace("L", L).Replace("AA", AA)));
                        }

                    }

                    if (BOMChildInfos.Count >= 2)
                    {
                        row4.GetCell(0).SetCellValue("2");
                        row4.GetCell(1).SetCellValue(BOMChildInfos[1].ChildNo);
                        row4.GetCell(2).SetCellValue(BOMChildInfos[1].ChildNumber);
                        row4.GetCell(4).SetCellValue(BOMChildInfos[1].M_Type);
                        row4.GetCell(5).SetCellValue(BOMChildInfos[1].Size);
                        row4.GetCell(6).SetCellValue(BOMChildInfos[1].Weight);
                        row4.GetCell(7).SetCellValue(BOMChildInfos[1].Number);
                        row4.GetCell(8).SetCellValue(BOMChildInfos[1].Remaker);
                        //运算公式
                        if (pars.Count >= 4)
                        {
                            row4.GetCell(10).SetCellValue(pars[2].Key);
                            row4.GetCell(11).SetCellValue(GetYMResult(pars[2].Cal.Replace("L", L).Replace("AA", AA)));
                            row4.GetCell(12).SetCellValue(pars[3].Key);
                            row4.GetCell(13).SetCellValue(GetYMResult(pars[3].Cal.Replace("L", L).Replace("AA", AA)));
                        }
                    }
                    var pics = JsonConvert.DeserializeObject<List<KeyValue<string>>>(bom.PictureJson).ToList();
                    if (pics.Count >= 1 && pics[0] != null)
                        SetPictureYM(System.IO.Directory.GetCurrentDirectory() + pics[0].Value, workBook, sheet, new XSSFClientAnchor(0, 0, 0, 0, 0, 5, 11, 16));
                    if (pics.Count >= 2 && pics[1] != null)
                        SetPictureYM(System.IO.Directory.GetCurrentDirectory() + pics[1].Value, workBook, sheet, new XSSFClientAnchor(0, 0, 0, 0, 0, 16, 11, 27));
                    if (pics.Count >= 3 && pics[2] != null)
                        SetPictureYM(System.IO.Directory.GetCurrentDirectory() + pics[2].Value, workBook, sheet, new XSSFClientAnchor(0, 0, 0, 0, 11, 5, 15, 16));
                    IRow row5 = sheet.GetRow(16);
                    row5.GetCell(11).SetCellValue("");

















                    try
                    {
                        workBook.Write(System.IO.File.OpenWrite(allPath + material[0].Material.ContractNo + ".xlsx"));
                    }
                    catch
                    {
                        MessageBox.Show("请先关闭之前创建的同名EXCEL文件");
                    }
                    ProcessStartInfo psi = new ProcessStartInfo
                    {
                        FileName = allPath,
                        UseShellExecute = true
                    };
                    Process.Start(psi);
                }



            }
            catch (Exception e)
            {
                MessageBox.Show("导出失败:" + e.Message);
            }
        }
        private static string GetYMResult(string cal)
        {
            string result = "";
            CalcOperation calcOperation = new CalcOperation();
            try
            {
                Convert.ToDecimal(cal);
                return cal;
            }
            catch (Exception ex)
            {
                result = calcOperation.CalcStr(cal).ToString();
                return result;

            }

        }

        private static void SetPictureYM(string url, IWorkbook workBook, ISheet sheet, XSSFClientAnchor anchor)
        {
            // 读取要添加的图片文件
            if (File.Exists(url))
            {
                byte[] bytes = File.ReadAllBytes(url);
                // 创建图片patriarch
                XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();

                int ip = workBook.AddPicture(bytes, PictureType.PNG);
                XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, ip);
            }
            else
            {
                //MessageBox.Show($@"{name}缺少图纸");
            }
        }






        /// <summary>
        /// 优迈总表
        /// </summary>
        /// <param name="orders"></param>
        public static void PrintingYMAll(List<Order> orders)
        {
            ExcelInfo excelInfo = new ExcelInfo();
            var boms = db.BOMs.ToList();
            var path = System.IO.Directory.GetCurrentDirectory();
            if (!Directory.Exists(path + "/Excel"))
            {
                Directory.CreateDirectory(path + "/Excel");
            }
            string readPath = path + "/Excel/" + "ymzb.xlsx";
            //读取模板文件
            using (FileStream fs = System.IO.File.OpenRead(readPath))
            {

                //【1】.创建工作簿 2007之前用HSSFWorkbook 2007之后用6XSSFWorkbook  
                IWorkbook? workBook = null;
                // 根据版本号创建不同版本的excel
                workBook = new XSSFWorkbook(fs);
                // 获取工作表
                ISheet sheet = workBook.GetSheetAt(0);
              

                // 创建单元格样式
                ICellStyle style = workBook.CreateCellStyle();

                // 创建字体
                IFont font = workBook.CreateFont();

                // 设置字体大小
                font.FontHeightInPoints = 9; // 设置为14号字体

                // 将字体应用到样式
                style.SetFont(font);
                List<MaterialViewModel> mv = new List<MaterialViewModel>();
                //订单
                foreach (var item in orders.GroupBy(t => t.OrderNo))
                {

                    List<OrderInfo> models = new List<OrderInfo>();
                    //对合同进行合同分类
                    foreach (var item1 in item)
                    {
                        OrderInfo viewModel = new OrderInfo();
                        viewModel.OrderNo = item1.OrderNo;

                        var m = db.Materials.Where(t => t.ContractNo == item1.ContractNo);
                        if (m != null && m.Count() > 0)
                        {
                            viewModel.Materials = m.ToList();
                            viewModel.ContractNo = m.ToList()[0].ContractNo;
                            viewModel.SchedulingDate = item1.SchedulingDate;
                            viewModel.DeliveryDate = item1.DeliveryDate;
                        }
                        models.Add(viewModel);
                    }
                    //合并
                    mv.AddRange(ListSame(models));







                }
                for (int i = 0; i < mv.Count; i++)
                {
                    var model = mv[i];
                    var row = sheet.CreateRow(i + 1);
                    row.RowStyle = style;
                    //下单日期
                    row.CreateCell(1).SetCellValue(model.SchedulingDate.ToString("M/d"));
                    //交期
                    row.CreateCell(2).SetCellValue(model.DeliveryDate.ToString("M/d"));
                    //订单号
                    row.CreateCell(3).SetCellValue(model.OrderNo);
                    //合同号
                    row.CreateCell(4).SetCellValue(model.ContractNo);
                    //外购件号
                    row.CreateCell(5).SetCellValue(model.Material.MaterialNo);
                    //名称
                    row.CreateCell(6).SetCellValue(model.Material.BoxName);
                    //数量
                    row.CreateCell(7).SetCellValue(model.Material.Number.ToString());
                    //材料
                    var bom = boms.FirstOrDefault(t => t.MaterialNo == model.Material.MaterialNo);
                    if(bom!=null)
                    {
                        row.CreateCell(8).SetCellValue(bom.ChannelSteel);
                   
                        //孔数
                        row.CreateCell(12).SetCellValue(bom.WireRopeHole);
                        //方向
                        row.CreateCell(15).SetCellValue(bom.Direction);
                    }
                    //参数
                    string par_str = string.IsNullOrEmpty(model.Material.MaterialSpecifications) ? model.Material.Remarks : model.Material.MaterialSpecifications;
                    row.CreateCell(9).SetCellValue(par_str);
                    //颜色
                    row.CreateCell(10).SetCellValue(model.Material.Color);



                }


                string allPath = path + "/Excel/" + DateTime.Now.ToString("yyyymmddhhMM") + "ymzb.xlsx";
                try
                {
                    workBook.Write(System.IO.File.OpenWrite(allPath));
                }
                catch (Exception e)
                {
                    MessageBox.Show("请先关闭之前创建的同名EXCEL文件");
                }
                ProcessStartInfo psi = new ProcessStartInfo
                {
                    FileName = allPath,
                    UseShellExecute = true
                };
                Process.Start(psi);

            }
            try
            {

             



            }
            catch (Exception e)
            {
                MessageBox.Show("导出失败:" + e.Message);
            }

        }


        public static List<MaterialViewModel> ListSame(List<OrderInfo> par)
        {
            List<MaterialViewModel> models1 = new List<MaterialViewModel>();
            List<KeyValue<string>> keys=new List<KeyValue<string>>();
            //对物料号进行排序组合
            par.ForEach(t => {
                if(t.Materials!=null)
                {
                    t.Materials = t.Materials.OrderBy(t => t.MaterialNo).ToList();
                    keys.Add(new KeyValue<string>() { Key = t.ContractNo, Value = string.Join(",", t.Materials.Select(t => t.MaterialNo)) });
                }
   
            }) ;
            //对合同号进行排序
            keys = keys.OrderBy(t => t.Key).ToList();
            // 同类项组合
            var group= keys.GroupBy(t => t.Value).ToList();
            for (int i = 0; i < group.Count; i++)
            {
                var list= group[i].ToList();
                if(list.Count>0)
                {
                    string head = list[0].Key.Substring(0, list[0].Key.Length-2);
                    string all = "";
                    int e = 0;

              
                    for (int j=0;j< list.Count;j++)
                    {
                        string head_c = list[j].Key.Substring(0, list[j].Key.Length - 2);
                        if(head_c!=head)
                        {
                            var orderinfo = par.FirstOrDefault(t => t.ContractNo == list[j].Key);
                            orderinfo.Materials.ForEach(m =>
                            {
                                models1.Add(new MaterialViewModel() { ContractNo = list[j].Key, OrderNo = par[0].Materials[0].OrderNo, Material = m,
                                    SchedulingDate= orderinfo.SchedulingDate, DeliveryDate= orderinfo.DeliveryDate
                                });
                            });
                        
                            continue;
                        }

                        int num = Convert.ToInt32(list[j].Key.Substring(list[j].Key.Length - 2, 2));
                        if(j==0)
                        {
                            all = num.ToString();
                        }
                        else
                        {
                            int num_b = Convert.ToInt32(list[j-1].Key.Substring(list[j-1].Key.Length - 2, 2));
                            if(num- num_b!=1||j==list.Count-1)
                            {
                                if(e!=0)
                                {
                                  
                                    all += num - e==1?("-"  + num) :("-" + e + "/" + num)  ;
                                    e = 0;
                                }
                                else
                                {
                                    all += "/" + num;
                                }
                                
                               
                            }
                            else
                            {
                                e = num;
                            }

                        }
                        


                    }
                    var orderinfo2 = par.FirstOrDefault(t => t.ContractNo == list[0].Key);
                    orderinfo2.Materials.ForEach(t =>
                    {
                        models1.Add(new MaterialViewModel() { ContractNo = head+ all, OrderNo = par[0].Materials[0].OrderNo, Material = t,
                            SchedulingDate = orderinfo2.SchedulingDate,
                            DeliveryDate = orderinfo2.DeliveryDate
                        });
                    });
                   
                }
            }

            return models1;



        }

        /// <summary>
        /// BOM主表导入
        /// </summary>
        /// <param name="fileName"></param>
        public static void ReadBOM()
        {
            try
            {
                string fileName = ExcelUserHelper. OpenExcelSystemDialog();
                using (FileStream fs = System.IO.File.OpenRead(fileName))
                {
                    //【1】.创建工作簿 2007之前用HSSFWorkbook 2007之后用6XSSFWorkbook  
                    IWorkbook? workBook = null;
                    // 根据版本号创建不同版本的excel
                    workBook = new XSSFWorkbook(fs);
                    // 获取工作表
                    ISheet sheet = workBook.GetSheet("参数一");
                    for (global::System.Int32 i = 1; i < sheet.LastRowNum; i++)
                    {
                        var row = sheet.GetRow(i);
                        if (row == null)
                            continue;
                        //获取编号
                        string no = sheet.GetRow(i).GetCell(19).CellToString();
                        var bOM = db.BOMs.FirstOrDefault(t => t.MaterialNo == no);
                        //存在则补全 //不存在则新增
                        if (bOM == null)
                        {
                            bOM = new BOM();
                        }


                        bOM.Company = 2;
                        bOM.ChannelSteel = sheet.GetRow(i).GetCell(20).CellToString();
                        bOM.ChannelSteelID = ExcelUserHelper.FindNSID(bOM.ChannelSteel);
                        bOM.Weight = sheet.GetRow(i).GetCell(21).CellToDouble();
                      
                        bOM.RibbedPlateType = sheet.GetRow(i).GetCell(23).CellToString();
                        bOM.RibbedPlateNumber = sheet.GetRow(i).GetCell(21).CellToDouble();
                        bOM.RibbedPlateWeight = sheet.GetRow(i).GetCell(22).CellToDouble();
                      
                        bOM.RopeHeadWeight = sheet.GetRow(i).GetCell(24).CellToDouble();
                        bOM.Counterweight = bOM.RibbedPlateWeight * bOM.RibbedPlateNumber + bOM.RopeHeadWeight * bOM.RopeHeadNumber;
                        if (!string.IsNullOrEmpty(no) && string.IsNullOrEmpty(bOM.MaterialNo))
                        {
                            bOM.MaterialNo = no;
                            db.BOMs.Add(bOM);
                        }


                    }
                    db.SaveChanges();

                    ISheet sheet2 = workBook.GetSheet("参数二");
                    for (global::System.Int32 i = 1; i < sheet2.LastRowNum; i++)
                    {
                        var row = sheet2.GetRow(i);
                        if (row == null)
                            continue;
                        //获取编号
                        string no = sheet2.GetRow(i).GetCell(0).CellToString();
                        var bOM = db.BOMs.FirstOrDefault(t => t.MaterialNo == no);
                        //存在则补全 //不存在则新增
                        if (bOM == null)
                        {
                            bOM = new BOM();
                        }



                        bOM.WireRopeHole = sheet2.GetRow(i).GetCell(1).CellToString();
                        bOM.MakeDirection = sheet2.GetRow(i).GetCell(9).CellToString();
                        bOM.Direction = sheet2.GetRow(i).GetCell(12).CellToString();
                      
                        if (!string.IsNullOrEmpty(no) && string.IsNullOrEmpty(bOM.MaterialNo))
                        {
                            bOM.MaterialNo = no;
                            db.BOMs.Add(bOM);
                        }


                    }
                    db.SaveChanges();
                }
            }
            catch (Exception e)
            {


            }
        }


    }
}
